package com.tedu.dao.impl;

import com.tedu.dao.EmpDao;
import com.tedu.entry.Dept;
import com.tedu.entry.Emp;
import com.tedu.entry.dto.EmpDTO;
import com.tedu.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Author：毕龙
 * Date: 2023/7/28 10:23
 */
public class EmpDaoImpl implements EmpDao {
    @Override
    public List<Emp> selectAllEmp() {

        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<Emp> list = new ArrayList<>();
        try {
            //1.获取链接
            conn = JDBCUtil.getConnection();

            //2.执行sql
            String sql = "select * from emp ";
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();

            //3.处理查询结果
            while (rs.next()){
               Emp emp = new Emp();
                emp.setId(rs.getInt(1))
                        .setName(rs.getString(2))
                        .setSalary(rs.getDouble(3))
                        .setBirthday(rs.getDate(4))
                        .setAge(rs.getInt(5))
                        .setDeptId(rs.getInt(6));
                list.add(emp);            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            //4.释放资源
            JDBCUtil.release(rs,pstm);
            JDBCUtil.release(conn);
        }

        return list;
    }

    @Override
    public int insertEmp(Emp emp) {
        Connection conn = null;
        PreparedStatement pstm = null;


        try {
            //1.获取链接
            conn = JDBCUtil.getConnection();

            //2.执行sql
            String sql = "insert into emp values (null,?,?,?,?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, emp.getName());
            pstm.setDouble(2, emp.getSalary());
            pstm.setDate(3,new Date(emp.getBirthday().getTime()));
            pstm.setInt(4,emp.getAge());
            pstm.setInt(5, emp.getDeptId());

            int i = pstm.executeUpdate();
            System.out.println("影响了"+i+"行");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            //4.释放资源
            JDBCUtil.release(pstm);
        }

        return 0;
    }

    @Override
    public int deleteEmpById(Integer id) {

        Connection conn = null;
        PreparedStatement pstm = null;


        try {
            //1.获取链接
            conn = JDBCUtil.getConnection();

            //2.执行sql
            String sql = "delete from emp where id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, id);


            int i = pstm.executeUpdate();
            System.out.println("影响了"+i+"行");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            //4.释放资源
            JDBCUtil.release(pstm);
        }

        return 0;

    }

    @Override
    public EmpDTO selectEmpById(Integer id) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        EmpDTO emp = null;
        try {
            //1.获取链接
            conn = JDBCUtil.getConnection();

            //2.执行sql
            String sql = "select * from emp e join dept d on e.dept_id = d.id where e.id = ? ";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            rs = pstm.executeQuery();

            //3.处理查询结果
            if(rs.next()){
                emp = new EmpDTO();
                emp.setId(rs.getInt(1))
                        .setName(rs.getString(2))
                        .setSalary(rs.getDouble(3))
                        .setBirthday(rs.getDate(4))
                        .setAge(rs.getInt(5))
                        .setDeptId(rs.getInt(6))
                        .setDept(new Dept().setName(rs.getString(8)));

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            //4.释放资源
            JDBCUtil.release(rs,pstm);

        }


        return emp;
    }


    @Override
    public int updateEmp(Emp emp) {
        Connection conn = null;
        PreparedStatement pstm = null;


        try {
            //1.获取链接
            conn = JDBCUtil.getConnection();

            //2.执行sql
            String sql = "update emp set name = ? , salary = ?, birthday = ?, age = ? ,dept_id = ? where id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, emp.getName());
            pstm.setDouble(2, emp.getSalary());
            pstm.setDate(3,new Date(emp.getBirthday().getTime()));
            pstm.setInt(4,emp.getAge());
            pstm.setInt(5, emp.getDeptId());
            pstm.setInt(6, emp.getId());

            int i = pstm.executeUpdate();
            System.out.println("影响了"+i+"行");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            //4.释放资源
            JDBCUtil.release(pstm);
        }

        return 0;
    }
}
